﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class PatientDataAccess
    {
        static MedicalCenterEntities entities = MedicalCenterEntities.Instance;

        public const string ConnString = "Data Source=.\\SQLEXPRESS;Initial Catalog=MedicalDatabase;Integrated Security = True";

        public static bool PatientRegister(Patient patient)
        {
            bool response = false;
            if (patient != null)
            {
               /*int i = DatabaseManager.Instance.ExecuteNonQuery(string.Format("insert into Patient values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", patient.PatientId, patient.FirstName, patient.FatherLastName, patient.MotherLastName, patient.LandPhone, patient.CellPhone, patient.Address));
                if (i == 0)
                    throw new Exception("The patient Id is registered.");*/
                entities.Patients.Add(patient);
                entities.SaveChanges();
                response = true;
            }
            return response;
        }


        public static DataTable ShowPatients(string patientId)
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = conn.CreateCommand();
                DataTable table = null;
                DataTable tableAux = null;
                cmd.CommandText = "SELECT PatientId,FirstName,FatherLastName,MotherLastName,GroupMember FROM [Patient] WHERE [PatientId] LIKE @PatientId + '%' OR [PatientId] LIKE '%' + @PatientId + '%' ORDER BY PatientId";
                cmd.Parameters.AddWithValue("PatientId", patientId);

                conn.Open();
                try
                {

                    SqlDataReader reader = cmd.ExecuteReader();
                    table = new DataTable();
                    table.Locale = CultureInfo.InvariantCulture;

                    table.Load(reader);
                    tableAux = table;

                    return tableAux;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error obteniendo el paciente", ex);
                }

                finally
                {
                    if (table != null)
                    {
                        table.Dispose();
                    }
                }
            }
        }


        public static ICollection<Patient> SearchPatient(string lastName)
        {
            if (!string.IsNullOrEmpty(lastName))
            {
                var patients = from patient in entities.Patients
                               where patient.FatherLastName == lastName
                               select patient;
                return patients.ToList();
            }
            return null;
            /*DataTable table = new DataTable();
            table = DatabaseManager.Instance.ExecuteQuery(string.Format("Select PatientId, FirstName, FatherLastName From Patient Where FatherLastName = '{0}'", LastName));
            return table;*/
        }

        public static Patient ShowPatient(string idPatient)
        {
            /*DataTable table = new DataTable();
            table = DatabaseManager.Instance.ExecuteQuery(string.Format("Select * From Patient Where PatientId = '{0}'", IdPatient));
            Patient patient = new Patient(table.Rows[0]["PatientId"].ToString(), table.Rows[0]["FirstName"].ToString(), table.Rows[0]["FatherLastName"].ToString(), table.Rows[0]["MotherLastName"].ToString(), table.Rows[0]["LandPhone"].ToString(), table.Rows[0]["CellPhone"].ToString(), table.Rows[0]["Address"].ToString());
            return patient;*/
            return entities.Patients.Find(idPatient);
        }

        public static bool PatientExists(string patientId)
        {
            Patient patient =entities.Patients.Find(patientId);

            return  patient != null;
        }

        public static bool MatchGroup(string value, string groupPatient)
        {
            if (!String.IsNullOrEmpty(value))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Patient] SET [GroupMember] = @GroupMember WHERE [PatientId] = @PatientId";
                    cmd.Parameters.AddWithValue("GroupMember", groupPatient);
                    cmd.Parameters.AddWithValue("PatientId", value);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error updating a patient in the database");
                        }
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error updating a patient in the database SQL Exception" + ex);
                    }

                }
            }
            else
                return false;
        }
    }
}
